﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace Sinacor.Infra.Core.Licensing.Data
{
    public class Empresa : ResourceAccess
    {
        public int SelecionarProximoIdEmpresa()
        {
            int retorno = 0;

            using (IDbCommand command = this.GetSqlStringCommand(" "))
            {
                string sql = " SELECT NVL(MAX(COD_CORR),0)+1 FROM TFWDCORR ";
                command.CommandText = sql.ToString();
                retorno = Convert.ToInt32(this.ExecuteScalar(command));
            }

            return retorno;
        }

        public Sinacor.Infra.Core.Licensing.Company SelecionarEmpresa(Sinacor.Infra.Core.Licensing.Company library)
        {
            Sinacor.Infra.Core.Licensing.Company response = null;

            StringBuilder sql = new StringBuilder();

            using (IDbCommand command = this.GetSqlStringCommand(" "))
            {
                #region Query
                sql.AppendLine(@" SELECT COD_CORR
                                        ,NOME_CORR
                                        ,COD_CNPJ_CORR
                                        ,COD_CORR_SINACOR
                                  FROM TFWDCORR
                                  WHERE (COD_CNPJ_CORR    = :COD_CNPJ_CORR) 
                                  AND   (COD_CORR_SINACOR = :COD_CORR_SINACOR) ");

                #endregion

                #region Filtro
                this.AddInParameter(command, "COD_CNPJ_CORR", DbType.String, library.LegalRegistrationId);
                this.AddInParameter(command, "COD_CORR_SINACOR", DbType.Int32, library.SinacorCompanyId);
                #endregion

                command.CommandText = sql.ToString();

                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        response = new Sinacor.Infra.Core.Licensing.Company
                        {
                            CompanyId = reader.GetValue<int>("COD_CORR"),
                            Name = reader.GetValue<string>("NOME_CORR"),
                            LegalRegistrationId = reader.GetValue<string>("COD_CNPJ_CORR"),
                            SinacorCompanyId = reader.GetValue<int>("COD_CORR_SINACOR")
                        };
                    }
                }
            }

            return response;
        }

        public List<Sinacor.Infra.Core.Licensing.Company> Selecionar(Sinacor.Infra.Core.Licensing.Company library)
        {
            List<Sinacor.Infra.Core.Licensing.Company> response = null;

            StringBuilder sql = new StringBuilder();

            using (IDbCommand command = this.GetSqlStringCommand(" "))
            {
                #region Query
                sql.AppendLine(@" SELECT COD_CORR
                                        ,NOME_CORR
                                        ,COD_CNPJ_CORR
                                        ,COD_CORR_SINACOR
                                  FROM TFWDCORR
                                  WHERE (COD_CORR            = :COD_CORR         OR :COD_CORR IS NULL) 
                                  AND   (NOME_CORR        LIKE :NOME_CORR        OR :NOME_CORR IS NULL) 
                                  AND   (COD_CNPJ_CORR       = :COD_CNPJ_CORR    OR :COD_CNPJ_CORR IS NULL) 
                                  AND   (COD_CORR_SINACOR    = :COD_CORR_SINACOR OR :COD_CORR_SINACOR IS NULL) ");

                #endregion

                #region Filtro
                this.AddInParameter(command, "COD_CORR", DbType.Int32, library.CompanyId);
                this.AddInParameter(command, "NOME_CORR", DbType.String, FitroEmpresa(library.Name));
                this.AddInParameter(command, "COD_CNPJ_CORR", DbType.String, library.LegalRegistrationId);
                this.AddInParameter(command, "COD_CORR_SINACOR", DbType.Int32, library.SinacorCompanyId);
                #endregion

                command.CommandText = sql.ToString();
                response = this.FindAll<Sinacor.Infra.Core.Licensing.Company>(command,
                    (
                #region Dados
reader => new Sinacor.Infra.Core.Licensing.Company
{
    CompanyId = reader.GetValue<int>("COD_CORR"),
    Name = reader.GetValue<string>("NOME_CORR"),
    LegalRegistrationId = reader.GetValue<string>("COD_CNPJ_CORR"),
    SinacorCompanyId = reader.GetValue<int>("COD_CORR_SINACOR")
}
                #endregion
)
                );
            }

            return response;
        }

        private string FitroEmpresa(string _empresa)
        {
            if (!string.IsNullOrEmpty(_empresa))
                _empresa = string.Format("%{0}%", _empresa);

            return _empresa;
        }

        public bool VerificarDependenciaLicenca(Sinacor.Infra.Core.Licensing.Company library)
        {
            bool response;
            int Total = 0;
            StringBuilder sql = new StringBuilder();

            using (IDbCommand command = this.GetSqlStringCommand(" "))
            {
                #region Query
                sql.AppendLine(@" SELECT COUNT(*) AS TOTAL 
                                  FROM TFWDLICE_MOD_CORR
                                  WHERE COD_CORR = :COD_CORR ");

                #endregion

                #region Filtro
                this.AddInParameter(command, "COD_CORR", DbType.Int32, library.CompanyId);
                #endregion

                command.CommandText = sql.ToString();
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Total = reader.GetValue<int>("TOTAL");
                    }
                }
                response = Total > 0 ? true : false;
            }

            return response;
        }

        public void Incluir(Sinacor.Infra.Core.Licensing.Company library)
        {
            StringBuilder sql = new StringBuilder();
            using (IDbCommand command = this.GetSqlStringCommand(" "))
            {
                #region Query
                sql.AppendLine(@" INSERT INTO TFWDCORR
                                            (COD_CORR
                                            ,NOME_CORR
                                            ,COD_CNPJ_CORR
                                            ,COD_CORR_SINACOR)
                                  VALUES
                                            (:COD_CORR
                                            ,:NOME_CORR
                                            ,:COD_CNPJ_CORR
                                            ,:COD_CORR_SINACOR) ");
                #endregion

                #region Dados
                this.AddInParameter(command, "COD_CORR", DbType.Int32, library.CompanyId);
                this.AddInParameter(command, "NOME_CORR", DbType.String, library.Name);
                this.AddInParameter(command, "COD_CNPJ_CORR", DbType.String, library.LegalRegistrationId);
                this.AddInParameter(command, "COD_CORR_SINACOR", DbType.Int32, library.SinacorCompanyId);
                #endregion

                command.CommandText = sql.ToString();
                ExecuteNonQuery(command);
            }
        }

        public void Alterar(Sinacor.Infra.Core.Licensing.Company library)
        {
            StringBuilder sql = new StringBuilder();
            using (IDbCommand command = this.GetSqlStringCommand(" "))
            {
                #region Query

                sql.AppendLine(@" UPDATE TFWDCORR SET 
                                     NOME_CORR        = :NOME_CORR
                                    ,COD_CNPJ_CORR    = :COD_CNPJ_CORR
                                    ,COD_CORR_SINACOR = :COD_CORR_SINACOR
                              WHERE COD_CORR = :COD_CORR ");
                #endregion

                #region Dados
                this.AddInParameter(command, "NOME_CORR", DbType.String, library.Name);
                this.AddInParameter(command, "COD_CNPJ_CORR", DbType.String, library.LegalRegistrationId);
                this.AddInParameter(command, "COD_CORR_SINACOR", DbType.Int32, library.SinacorCompanyId);
                #endregion

                #region Filtro
                this.AddInParameter(command, "COD_CORR", DbType.Int32, library.CompanyId);
                #endregion

                command.CommandText = sql.ToString();
                ExecuteNonQuery(command);
            }
        }

        public void Excluir(Sinacor.Infra.Core.Licensing.Company library)
        {
            using (IDbCommand command = this.GetSqlStringCommand(" "))
            {
                #region Query
                string sql = " DELETE FROM TFWDCORR WHERE COD_CORR = :COD_CORR ";
                #endregion

                #region Filtro
                this.AddInParameter(command, "COD_CORR", DbType.Int32, library.CompanyId);
                #endregion

                command.CommandText = sql.ToString();
                this.ExecuteNonQuery(command);
            }
        }

        public void ExcluirLicencaEmpresaModulo(Sinacor.Infra.Core.Licensing.LicensingCompanyModule library)
        {
            StringBuilder sql = new StringBuilder();
            using (IDbCommand command = this.GetSqlStringCommand(" "))
            {
                sql.AppendLine(@" DELETE FROM TFWDLICE_MOD_CORR WHERE COD_LICE = :COD_LICE ");
                this.AddInParameter(command, "COD_LICE", DbType.Int32, library.LicensingId);
                command.CommandText = sql.ToString();
                this.ExecuteNonQuery(command);
            }
        }

		//*--------------------------------------------------------------------------------

		public int HasAliasLicense(int aliasId)
		{
			StringBuilder sql = new StringBuilder();

			using (IDbCommand command = this.GetSqlStringCommand(" "))
			{
				sql.AppendLine(@"SELECT COD_LICE FROM TFWDLICE WHERE COD_ALIA = :COD_ALIA");

				this.AddInParameter(command, "COD_ALIA", DbType.Int32, aliasId);

				command.CommandText = sql.ToString();
				object retorno = command.ExecuteScalar();
				if (retorno != null)
					return Convert.ToInt32(command.ExecuteScalar());
				else
					return 0;
			}
		}

		public List<Sinacor.Infra.Core.Licensing.Module> RetrieveModules()
		{
			List<Module> modules = new List<Module>();

			StringBuilder sql = new StringBuilder();

			using (IDbCommand command = this.GetSqlStringCommand(""))
			{
                sql.AppendLine(@"SELECT MODL.COD_MOD_LOGC,MODL.NOME_MOD_LOGC, MODL.SIGL_MOD_LOGC FROM TFWDMOD_LOGC MODL");
				sql.AppendLine("ORDER BY MODL.NOME_MOD_LOGC");

				command.CommandText = sql.ToString();

				modules = this.FindAll<Sinacor.Infra.Core.Licensing.Module>(command,
					(
						reader => new Sinacor.Infra.Core.Licensing.Module
						{
							ID = reader.GetValue<int>("COD_MOD_LOGC"),
							Name = reader.GetValue<string>("NOME_MOD_LOGC"),
                            Monogram = reader.GetValue<string>("SIGL_MOD_LOGC")
						}
					)
				);

				return modules;
			}
		}

        public bool VerificarEmpresaExiste(Sinacor.Infra.Core.Licensing.Company library)
        {
            bool response;
            int Total = 0;
            StringBuilder sql = new StringBuilder();

            using (IDbCommand command = this.GetSqlStringCommand(" "))
            {
                sql.AppendLine(@" SELECT COUNT(*) AS TOTAL 
                                  FROM TFWDCORR
                                  WHERE (NOME_CORR    = :NOME_CORR) 
                                  AND   (COD_CNPJ_CORR = :COD_CNPJ_CORR)");

                this.AddInParameter(command, "NOME_CORR", DbType.String, library.Name);
                this.AddInParameter(command, "COD_CNPJ_CORR", DbType.String, library.LegalRegistrationId);

                command.CommandText = sql.ToString();
                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Total = reader.GetValue<int>("TOTAL");
                    }
                }
                response = Total > 0 ? true : false;
            }

            return response;
        }

        public Sinacor.Infra.Core.Licensing.Company SelecionarCodEmpresa(Sinacor.Infra.Core.Licensing.Company library)
        {
            Sinacor.Infra.Core.Licensing.Company response = null;

            StringBuilder sql = new StringBuilder();

            using (IDbCommand command = this.GetSqlStringCommand(" "))
            {
                #region Query
                sql.AppendLine(@" SELECT COD_CORR
                                        ,NOME_CORR
                                        ,COD_CNPJ_CORR
                                        ,COD_CORR_SINACOR
                                  FROM TFWDCORR
                                  WHERE (NOME_CORR    = :NOME_CORR) 
                                  AND   (COD_CNPJ_CORR = :COD_CNPJ_CORR) ");

                #endregion

                #region Filtro
                this.AddInParameter(command, "NOME_CORR", DbType.String, library.Name);
                this.AddInParameter(command, "COD_CNPJ_CORR", DbType.String, library.LegalRegistrationId);
                #endregion

                command.CommandText = sql.ToString();

                using (IDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        response = new Sinacor.Infra.Core.Licensing.Company
                        {
                            CompanyId = reader.GetValue<int>("COD_CORR"),
                            Name = reader.GetValue<string>("NOME_CORR"),
                            LegalRegistrationId = reader.GetValue<string>("COD_CNPJ_CORR"),
                            SinacorCompanyId = reader.GetValue<int>("COD_CORR_SINACOR")
                        };
                    }
                }
            }

            return response;
        }
    }
}